﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DotNet.Utilities;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace YunHan.BuShi123.DAL
{
    public class BuShi123SqlHelper : SqlHelper
    {
        /// <summary>
        /// Database connection strings
        /// </summary>
        public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

        #region ExecuteNonQuery
        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  int result = ExecuteNonQuery("PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteNonQuery(ConnectionString, System.Data.CommandType.Text, cmdText, commandParameters);
        }

        public static int ExecuteNonQuery(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteNonQuery(trans, System.Data.CommandType.Text, cmdText, commandParameters);
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  SqlDataReader r = ExecuteReader("PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, cmdText, commandParameters);
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar("PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, cmdText, commandParameters);
        }

        public static object ExecuteScalar(SqlTransaction trans, string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteScalar(trans, System.Data.CommandType.Text, cmdText, commandParameters);
        }
        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the database specified in the connection string 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  DataSet ds = ExecuteDataSet("PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] commandParameters)
        {
            return SqlHelper.ExecuteDataSet(ConnectionString, CommandType.Text, cmdText, commandParameters);
        }
        #endregion

        #region CreateSqlTransaction
        /// <summary>
        /// 创建一个事务对象
        /// </summary>
        /// <returns></returns>
        public static SqlTransaction CreateSqlTransaction()
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            return conn.BeginTransaction();
        }

        /// <summary>
        /// 创建一个事务对象
        /// </summary>
        /// <param name="level">指定事务锁定行为</param>
        /// <returns></returns>
        public static SqlTransaction CreateSqlTransaction(IsolationLevel level)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            return conn.BeginTransaction(level, "trans");
        }
        #endregion

        public static DataTable GetListPage(int pageSize, int pageIndex, ref int recordCout, string from, string field, string where, string orderby, params SqlParameter[] parms)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from");
            sql.AppendFormat("(select row_number()over(order by {})rownumber, {1} from {0})a", from, field);
            sql.AppendFormat("where rownumber between {0} and {1}", pageSize * (pageIndex - 1) + 1, pageIndex * pageSize);

            DataSet ds = SqlHelper.ExecuteDataSet(ConnectionString, CommandType.Text, sql.ToString(), parms);

            return ds.Tables[0];
        }

        /// <summary>
        /// 取得DateTime值
        /// </summary>
        public static DateTime? GetDateTime(object obj)
        {
            if (obj != null && obj != DBNull.Value)
            {
                DateTime result;
                if (DateTime.TryParse(obj.ToString(), out result))
                    return result;
                else
                    return null;
            }
            else
            {
                return null;
            }
        }

    }
}
